import akshare as ak
import datetime
import SimpleMySqlClass as smc


def get_all_fund_info(code):
    etf_list = ak.fund_etf_category_sina(symbol=code)
    print(etf_list)
    etf_list = etf_list.loc[:, ["代码", "名称"]]
    etf_list.columns = ["symbol", "name"]
    # etf_list["code"] = etf_list.loc[:, "symbol"]
    # ak.fund_etf_hist_em()
    return etf_list


# 获取数据库链接
def get_mysql_conn():
    conn = smc.SimpleMySqlClass("127.0.0.1", "stocks", "haojg", "haojg")
    return conn


def get_data_and_read(code, tableName):
    # 拉取远端数据
    stock_qfq_data = get_all_fund_info(code)
    # 获取链接
    conn = get_mysql_conn()
    # 落库
    save_data_to_mysql(conn, stock_qfq_data, tableName)
    # 读数
    ct = conn.count(tableName)
    print("ct = ", ct)
    # 关闭连接
    conn.close()


# 2, save data to mysql
def save_data_to_mysql(conn, stock_data_df, tableName):
    # table = "t_hs_300"
    for index, row in stock_data_df.iterrows():
        code = row["symbol"].replace("sz", "").replace("sh", "")
        symbol = row["symbol"]
        name = row["name"]
        # 插入新记录
        insert_data = {
            "id": 0,
            "symbol": symbol,
            "code": code,
            "name": name,
            "create_time": datetime.datetime.now(),
            "update_time": datetime.datetime.now(),
        }
        in_rs = conn.insertIgnore(tableName, insert_data)
        print("insert rs = ", in_rs)

        update_data = {
            "code": code,
            "status": "Y",
            "update_time": datetime.datetime.now(),
        }
        where_condition = " code = '" + code + "'"
        up_rs = conn.update(tableName, update_data, where_condition)
        print("update rs = ", up_rs)

    print("-------------------------------")
    return


# 3, query data from mysql
def read_data_from_mysql(conn, tableName):
    sql = f"select count(*) from {tableName}"
    rs = conn.get_one(sql)
    print("query rs = ", rs)
    pass


if __name__ == "__main__":
    # 封闭式基金": "close_fund",
    # ETF基金": "etf_hq_fund",
    # LOF基金": "lof_hq_fund",
    cfg_code_table_arr = [
        {"code": "ETF基金", "desc": "ETF基金", "tableName": "t_fund_etf"},
        {"code": "LOF基金", "desc": "ETF基金", "tableName": "t_fund_lof"},
    ]
    for cct in cfg_code_table_arr:
        code = cct["code"]
        tableName = cct["tableName"]
        desc = cct["desc"]
        print(f"开始获取{ desc} 数据, code = {code}, tableName={tableName}")
        get_data_and_read(code, tableName)
    print("end")
